SELECT t_org.code    AS org_code,
t_org.name         AS org_name,
T_FLAG.code        AS FLAG_CODE,
T_FLAG.NAME        AS FLAG_NAME,
T_SEX.code         AS SEX_CODE,
T_SEX.NAME         AS SEX_NAME,
T_DIPLOMA.code     AS DIPLOMA_CODE,
T_DIPLOMA.NAME     AS DIPLOMA_NAME,
T_PROFESSION.code  AS PROFESSION_CODE,
T_PROFESSION.NAME  AS PROFESSION_NAME,
T_MARRY_STATE.code AS MARRY_STATE_CODE,
T_MARRY_STATE.NAME AS MARRY_STATE_NAME,
T_FOLK.code        AS FOLK_CODE,
T_FOLK.NAME        AS FOLK_NAME,
T_COUNTRY.code     AS COUNTRY_CODE,
T_COUNTRY.NAME     AS COUNTRY_NAME,
T_PROVINCE.code    AS PROVINCE_CODE,
T_PROVINCE.NAME    AS PROVINCE_NAME,
T_CITY.code        AS CITY_CODE,
T_CITY.NAME        AS CITY_NAME,
T_DISTRICT.code    AS DISTRICT_CODE,
T_DISTRICT.NAME    AS DISTRICT_NAME,
t.*,
TRUNC(MONTHS_BETWEEN(SYSDATE, NVL(T.BIRTHDAY,SYSDATE))/12) AS now_age,
TO_CHAR(T.END_DATE, 'yyyy-mm-dd')                          AS end_date_str,
(
CASE
WHEN (SYSDATE BETWEEN NVL(T.BEGIN_DATE, SYSDATE) AND NVL(T.END_DATE, SYSDATE))
THEN '1'
ELSE '2'
END) AS valid_code ,
(SELECT NVL(MAX(s.CREATED_DATE), SYSDATE)
FROM C1_ENTITY_LOG s
WHERE s.code       = 'login'
AND s.entity_id    = :id
AND s.CREATED_DATE <
(SELECT NVL(MAX(ss.CREATED_DATE), SYSDATE)
FROM C1_ENTITY_LOG ss
WHERE ss.code    = 'login'
AND ss.entity_id = :id
)
) AS last_Login_Date
FROM c1_user t
LEFT JOIN C1_ORG T_ORG
ON (T_ORG.id = t.Org_ID)
LEFT JOIN c1_dictionary T_FLAG
ON (T_FLAG.id = t.FLAG_ID)
LEFT JOIN c1_dictionary T_SEX
ON (T_SEX.id = t.SEX_ID)
LEFT JOIN c1_dictionary T_DIPLOMA
ON (T_DIPLOMA.id = t.DIPLOMA_ID)
LEFT JOIN c1_dictionary T_PROFESSION
ON (T_PROFESSION.id = t.PROFESSION_ID)
LEFT JOIN c1_dictionary T_MARRY_STATE
ON (T_MARRY_STATE.id = t.MARRY_STATE_ID)
LEFT JOIN c1_dictionary T_FOLK
ON (T_FOLK.id = t.FOLK_ID)
LEFT JOIN c1_dictionary T_COUNTRY
ON (T_COUNTRY.id = t.COUNTRY_ID)
LEFT JOIN c1_dictionary T_PROVINCE
ON (T_PROVINCE.id = t.PROVINCE_ID)
LEFT JOIN c1_dictionary T_CITY
ON (T_CITY.id = t.CITY_ID)
LEFT JOIN c1_dictionary T_DISTRICT
ON (T_DISTRICT.id = t.DISTRICT_ID)
WHERE T.ID        = :id